//
//  D_DataBase.m
//  音悦咖
//
//  Created by dllo on 15/5/1.
//  Copyright (c) 2015年 dllo. All rights reserved.
//

#import "D_DataBase.h"

@implementation D_DataBase

+(D_DataBase *)shareInstance
{
    // 单例
    static D_DataBase *dbManager = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        
        dbManager = [[D_DataBase alloc]init];
    });
    return dbManager;
}

// 创建打开数据库
- (void)openDB
{
    // 数据库放在documents文件下
    
    // 获取documents路径
    NSString *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
    NSLog(@"-----------%@",path);
    
    NSString *dbpath = [path stringByAppendingPathComponent:@"mymusic.db"];
    
    int result = sqlite3_open([dbpath UTF8String], &dbPoint);
    
    NSLog(@"------%d",result);
}

// 关闭数据库
- (void)closeDB
{
    sqlite3_close(dbPoint);
}

// 创建表

- (void)createTable
{
    
    
    /**
     *   Musci
     */
    NSString *creatSQL = @"create table if not exists mylove (song_name text, singer_name text, url text,song_id text,pic_url text,duration_time text ,num integer primary key autoincrement)";
    
    NSString *creatsq = @"create table if not exists local (song_name text, singer_name text, url text, song_id text,pic_url text,duration_time text , num integer primary key autoincrement)";
    NSString *creatcollect = @"create table if not exists collect (song_name text, singer_name text, url text, song_id text,pic_url text, duration_time text ,num integer primary key autoincrement)";
    /**
     *  Recent
     */
    NSString *createRecentSQL = @"create table if not exists recent (title text, count text, recent_url text, num integer primary key autoincrement)";
    
    
    
    int result = sqlite3_exec(dbPoint, creatSQL.UTF8String, NULL, NULL, NULL);
    int res = sqlite3_exec(dbPoint, creatsq.UTF8String, NULL, NULL, NULL);
    int collect = sqlite3_exec(dbPoint, creatcollect.UTF8String, NULL, NULL, NULL);
    
    int recent = sqlite3_exec(dbPoint, createRecentSQL.UTF8String, NULL, NULL, NULL);

     [self judgeResult:collect type:@"创建我的收藏表"];
    [self judgeResult:res type:@"创建本地表"];
    [self judgeResult:result type:@"创建表"];
    [self judgeResult:recent type:@"创建最近浏览表"];
}


- (void)judgeResult:(int)r type:(NSString *)type
{
    
    if (r == SQLITE_OK) {
        NSLog(@"%@操作成功",type);
    }else{
        NSLog(@"%@操作错误,原因%d",type,r);
    }
}


// 插入歌曲
- (void)insertInforMyMusic:(D_MyMusic *)myMusic
{
     NSString *insertSQL = [NSString stringWithFormat:@"insert into mylove (song_name, singer_name, url,song_id,pic_url,duration_time) values ('%@','%@','%@','%@','%@','%@')", myMusic.song_name,myMusic.singer_name,myMusic.url,myMusic.song_id,myMusic.pic_url,myMusic.duration_time];
    
    int result = sqlite3_exec(dbPoint, insertSQL.UTF8String, NULL, NULL, NULL);
    
    [self judgeResult:result type:@"插入"];
    
}

// 插入我的收藏歌曲
- (void)insertMyCollect:(D_MyMusic *)collect
{
    NSString *insertSQL = [NSString stringWithFormat:@"insert into collect (song_name, singer_name, url,song_id,pic_url,duration_time) values ('%@','%@','%@','%@','%@','%@')", collect.song_name,collect.singer_name,collect.url,collect.song_id,collect.pic_url,collect.duration_time];
    
    int result = sqlite3_exec(dbPoint, insertSQL.UTF8String, NULL, NULL, NULL);
    
    [self judgeResult:result type:@"插入"];
    
}



// 插入本地数据
- (void)insertLocalMyMusic:(D_MyMusic *)local
{
    NSString *insertSQL = [NSString stringWithFormat:@"insert into local (song_name, singer_name, url,song_id,pic_url,duration_time) values ('%@','%@','%@','%@','%@','%@')", local.song_name,local.singer_name,local.url,local.song_id,local.pic_url,local.duration_time];
    
    int result = sqlite3_exec(dbPoint, insertSQL.UTF8String, NULL, NULL, NULL);
    
    [self judgeResult:result type:@"插入"];
}


// 增减新的浏览记录
- (void)insertInforRecentSkim:(C_RecentSkimModel *)myRecent
{
  
    NSString *insertSQL = [NSString stringWithFormat:@"insert into recent (title, count, recent_url) values ('%@', '%@', '%@')", myRecent.title, myRecent.count, myRecent.recent_url];
    
    int result = sqlite3_exec(dbPoint, insertSQL.UTF8String, NULL, NULL, NULL);
    
    [self judgeResult:result type:@"插入新的浏览"];
    
}
//删除浏览记录

- (void)deleteFromTitle:(NSString *)title
{
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from recent where title = '%@',", title];
    int result = sqlite3_exec(dbPoint, deleteSQL.UTF8String, NULL, NULL, NULL);
    [self judgeResult:result type:@"删除浏览记录"];
}


// 删除我喜欢歌曲
- (void)deleteInforWithNum:(NSString *)song_name
{
    
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from mylove where song_name = '%@'",song_name];
 
    int result = sqlite3_exec(dbPoint, deleteSQL.UTF8String, NULL, NULL, NULL);
 
    [self judgeResult:result type:@"删除我喜欢"];
    
}
// 删除收藏歌曲
- (void)deleteCollection:(NSString *)song_name
{
    
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from collect where song_name = '%@'",song_name];
    
    int result = sqlite3_exec(dbPoint, deleteSQL.UTF8String, NULL, NULL, NULL);
    
    [self judgeResult:result type:@"删除收藏歌曲"];
    
}

// 删除本地歌曲
- (void)deleteLocal:(NSString *)song_name
{
    
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from local where song_name = '%@'",song_name];
    
    int result = sqlite3_exec(dbPoint, deleteSQL.UTF8String, NULL, NULL, NULL);
    
    [self judgeResult:result type:@"删除本地歌曲"];
    
}


// 查询我喜欢信息
- (NSMutableArray *)selectInfor
{
    NSString *selectSQL = @"select * from mylove";
    sqlite3_stmt *stmt = nil;
   
    int result = sqlite3_prepare_v2(dbPoint, selectSQL.UTF8String, -1, &stmt, nil);
    
    // 4. 创建用于返回数据的数组
    NSMutableArray *arr = [NSMutableArray array];
    
    // 5. 判断查询准备是否成功
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
        // 6. 开始遍历每一行信息
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 当数据库替身指向的数据符合查询条件 在while中返回
            // 逐行获取每一列信息
            // 列数 从0开始
            const unsigned char *song_name = sqlite3_column_text(stmt, 0);
           const unsigned char *singer_name = sqlite3_column_text(stmt, 1);
            const unsigned char *url = sqlite3_column_text(stmt, 2);
            const unsigned char *song_id = sqlite3_column_text(stmt, 3);
            const unsigned char *pic_url = sqlite3_column_text(stmt, 4);
            const unsigned char *duration_time = sqlite3_column_text(stmt, 5);
            // 把获取到的数据信息保存在model中
            D_MyMusic *temp = [[D_MyMusic alloc]init];
            temp.song_name = [NSString stringWithUTF8String:(const char *)song_name];
            temp.singer_name = [NSString stringWithUTF8String:(const char *)singer_name];
            temp.url = [NSString stringWithUTF8String:(const char *)url];
            temp.song_id = [NSString stringWithUTF8String:(const char *)song_id];
            temp.pic_url = [NSString stringWithUTF8String:(const char *)pic_url];
            temp.duration_time = [NSString stringWithUTF8String:(const char *)duration_time];
            // 添加到数组中
            [arr addObject:temp];
            [temp release];
            
        }
    }else{
        NSLog(@"查询准备失败,原因:%d",result);
    }
    
    return  arr;

}

// 查询本地音乐
- (NSMutableArray *)selectLocal
{
    NSString *selectSQL = @"select * from local";
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(dbPoint, selectSQL.UTF8String, -1, &stmt, nil);
 
    NSMutableArray *arr = [NSMutableArray array];
 
    if (result == SQLITE_OK) {
        NSLog(@"查询本地准备成功");
        // 6. 开始遍历每一行信息
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 当数据库替身指向的数据符合查询条件 在while中返回
            // 逐行获取每一列信息
            // 列数 从0开始
            const unsigned char *song_name = sqlite3_column_text(stmt, 0);
            const unsigned char *singer_name = sqlite3_column_text(stmt, 1);
            const unsigned char *url = sqlite3_column_text(stmt, 2);
            const unsigned char *song_id = sqlite3_column_text(stmt, 3);
            const unsigned char *pic_url = sqlite3_column_text(stmt, 4);
            const unsigned char *duration_time = sqlite3_column_text(stmt, 5);
            
            // 把获取到的数据信息保存在model中
            D_MyMusic *temp = [[D_MyMusic alloc]init];
            temp.song_name = [NSString stringWithUTF8String:(const char *)song_name];
            temp.singer_name = [NSString stringWithUTF8String:(const char *)singer_name];
            temp.url = [NSString stringWithUTF8String:(const char *)url];
            temp.song_id = [NSString stringWithUTF8String:(const char *)song_id];
            temp.pic_url = [NSString stringWithUTF8String:(const char *)pic_url];
            temp.duration_time = [NSString stringWithUTF8String:(const char *)duration_time];
            
            // 添加到数组中
            [arr addObject:temp];
            [temp release];
            
        }
    }else{
        NSLog(@"查询准备失败,原因:%d",result);
    }
    
    return  arr;

}


// 查询收藏

- (NSMutableArray *)selectCollect
{
    NSString *selectSQL = @"select * from collect";
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(dbPoint, selectSQL.UTF8String, -1, &stmt, nil);
    
    NSMutableArray *arr = [NSMutableArray array];
    
    if (result == SQLITE_OK) {
        NSLog(@"查询本地准备成功");
        // 6. 开始遍历每一行信息
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 当数据库替身指向的数据符合查询条件 在while中返回
            // 逐行获取每一列信息
            // 列数 从0开始
            const unsigned char *song_name = sqlite3_column_text(stmt, 0);
            const unsigned char *singer_name = sqlite3_column_text(stmt, 1);
            const unsigned char *url = sqlite3_column_text(stmt, 2);
            const unsigned char *song_id = sqlite3_column_text(stmt, 3);
            const unsigned char *pic_url = sqlite3_column_text(stmt, 4);
            const unsigned char *duration_time = sqlite3_column_text(stmt, 5);
            
            // 把获取到的数据信息保存在model中
            D_MyMusic *temp = [[D_MyMusic alloc]init];
            temp.song_name = [NSString stringWithUTF8String:(const char *)song_name];
            temp.singer_name = [NSString stringWithUTF8String:(const char *)singer_name];
            temp.url = [NSString stringWithUTF8String:(const char *)url];
            temp.song_id = [NSString stringWithUTF8String:(const char *)song_id];
            temp.pic_url = [NSString stringWithUTF8String:(const char *)pic_url];
            temp.duration_time = [NSString stringWithUTF8String:(const char *)duration_time];
            
            // 添加到数组中
            [arr addObject:temp];
            [temp release];
            
        }
    }else{
        NSLog(@"查询准备失败,原因:%d",result);
    }
    
    return  arr;
}

//查询浏览记录信息
- (NSMutableArray *)selectInforRecent
{
       NSString *selectSQL = @"select * from recent";
      sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, selectSQL.UTF8String, -1, &stmt, nil);
    NSMutableArray *arr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *title = sqlite3_column_text(stmt, 0);
            const unsigned char *count = sqlite3_column_text(stmt, 1);
            const unsigned char *recent_url = sqlite3_column_text(stmt, 2);
            C_RecentSkimModel *temp = [[C_RecentSkimModel alloc] init];
            temp.title = [NSString stringWithUTF8String:(const char *)title];
            temp.count = [NSString stringWithUTF8String:(const char *)count];
            temp.recent_url = [NSString stringWithUTF8String:(const char *)recent_url];
            [arr addObject:temp];
            [temp release];
        }
    } else {
        NSLog(@"查询准备失败, 失败原因:%d", result);
    }
    return arr;
}


// 删除表
- (void)deleteTable
{
    NSString *dropSQL = @"delete from local";
    
    int result = sqlite3_exec(dbPoint, dropSQL.UTF8String, NULL, NULL, NULL);
    [self judgeResult:result type:@"删除表"];
}



//删除Recent table
- (void)deleteRecentTable
{
     NSString *dropSQL = @"delete from recent";
    
    int result = sqlite3_exec(dbPoint, dropSQL.UTF8String, NULL, NULL, NULL);
    
    [self judgeResult:result type:@"删除Recent表"];
}
// 删除所有表
- (void) deleteAllTable
{
    NSString *drop1 = @"drop table mylove";
    NSString *drop2 = @"drop table local";
    NSString *drop3 = @"drop table collect";
    
    int result1 = sqlite3_exec(dbPoint, drop1.UTF8String, NULL, NULL, NULL);
    int result2 = sqlite3_exec(dbPoint, drop2.UTF8String, NULL, NULL, NULL);
    int result3 = sqlite3_exec(dbPoint, drop3.UTF8String, NULL, NULL, NULL);
    
    [self judgeResult:result1 type:@"删除所有表"];
    [self judgeResult:result2 type:@"删除所有表"];
    [self judgeResult:result3 type:@"删除所有表"];
}



@end
